"""
统计功能路由
"""
import io
from datetime import datetime, timedelta
from flask import Blueprint, render_template, request, send_file
from flask_login import login_required
from sqlalchemy import func, or_
import xlsxwriter

from app.extensions import db
from app.models.customer import Customer
from app.models.staff import Staff
from app.models.negotiation import Negotiation

statistics_bp = Blueprint('statistics', __name__, url_prefix='/statistics')


@statistics_bp.route('/by_staff')
@login_required
def stats_by_staff():
    """按项目负责人统计"""
    staff_name = request.args.get('staff_name', '')
    customer_name = request.args.get('customer_name', '')
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    show_online = request.args.get('show_online', 'on') == 'on'
    show_offline = request.args.get('show_offline', 'on') == 'on'
    
    results = []
    
    if staff_name and start_date and end_date:
        # 构建查询条件
        query = Negotiation.query
        
        # 按负责人筛选（支持多个负责人的情况）
        query = query.filter(Negotiation.staff_names.contains(staff_name))
        
        if customer_name:
            query = query.filter(Negotiation.customer_name.contains(customer_name))
        
        # 时间范围筛选
        start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        query = query.filter(
            Negotiation.start_time >= start_datetime,
            Negotiation.start_time < end_datetime
        )
        
        # 按客户分组统计
        customers = query.with_entities(Negotiation.customer_name).distinct().all()
        
        for customer_tuple in customers:
            customer = customer_tuple[0]
            
            # 线上商谈统计
            online_negotiations = query.filter(
                Negotiation.customer_name == customer,
                Negotiation.category == '线上合作商谈'
            ).all()
            
            online_total_duration = sum(n.duration_minutes or 0 for n in online_negotiations)
            online_count = len(online_negotiations)
            online_avg_duration = online_total_duration / online_count if online_count > 0 else 0
            
            # 线下商谈统计
            offline_negotiations = query.filter(
                Negotiation.customer_name == customer,
                Negotiation.category == '线下合作商谈'
            ).all()
            
            offline_total_duration = sum(n.duration_minutes or 0 for n in offline_negotiations)
            offline_count = len(offline_negotiations)
            offline_avg_duration = offline_total_duration / offline_count if offline_count > 0 else 0
            
            # 总计统计
            total_duration = online_total_duration + offline_total_duration
            total_count = online_count + offline_count
            total_avg_duration = total_duration / total_count if total_count > 0 else 0
            
            results.append({
                'staff_name': staff_name,
                'customer_name': customer,
                'online_total_duration': online_total_duration,
                'online_count': online_count,
                'online_avg_duration': online_avg_duration,
                'offline_total_duration': offline_total_duration,
                'offline_count': offline_count,
                'offline_avg_duration': offline_avg_duration,
                'total_duration': total_duration,
                'total_count': total_count,
                'total_avg_duration': total_avg_duration
            })
    
    # 获取下拉选项数据
    staff_names = db.session.query(Staff.name).all()
    customer_names = db.session.query(Customer.name).all()
    
    return render_template('statistics/stats_by_staff.html',
                         results=results,
                         staff_names=[s[0] for s in staff_names],
                         customer_names=[c[0] for c in customer_names],
                         staff_name=staff_name,
                         customer_name=customer_name,
                         start_date=start_date,
                         end_date=end_date,
                         show_online=show_online,
                         show_offline=show_offline)


@statistics_bp.route('/by_customer')
@login_required
def stats_by_customer():
    """按客户统计"""
    customer_name = request.args.get('customer_name', '')
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    
    results = []
    
    if customer_name and start_date and end_date:
        # 时间范围
        start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        
        # 查询该客户在时间范围内的所有商谈记录
        negotiations = Negotiation.query.filter(
            Negotiation.customer_name == customer_name,
            Negotiation.start_time >= start_datetime,
            Negotiation.start_time < end_datetime
        ).all()
        
        if negotiations:
            # 线上商谈统计
            online_negotiations = [n for n in negotiations if n.category == '线上合作商谈']
            online_total_duration = sum(n.duration_minutes or 0 for n in online_negotiations)
            online_count = len(online_negotiations)
            online_avg_duration = online_total_duration / online_count if online_count > 0 else 0
            
            # 线下商谈统计
            offline_negotiations = [n for n in negotiations if n.category == '线下合作商谈']
            offline_total_duration = sum(n.duration_minutes or 0 for n in offline_negotiations)
            offline_count = len(offline_negotiations)
            offline_avg_duration = offline_total_duration / offline_count if offline_count > 0 else 0
            
            # 总计统计
            total_duration = online_total_duration + offline_total_duration
            total_count = online_count + offline_count
            total_avg_duration = total_duration / total_count if total_count > 0 else 0
            
            # 计算时间段天数
            time_delta = (end_datetime - start_datetime).days
            avg_daily_negotiations = total_count / time_delta if time_delta > 0 else 0
            
            results.append({
                'customer_name': customer_name,
                'online_total_duration': online_total_duration,
                'online_count': online_count,
                'online_avg_duration': online_avg_duration,
                'offline_total_duration': offline_total_duration,
                'offline_count': offline_count,
                'offline_avg_duration': offline_avg_duration,
                'total_duration': total_duration,
                'total_count': total_count,
                'total_avg_duration': total_avg_duration,
                'avg_daily_negotiations': avg_daily_negotiations,
                'time_period_days': time_delta
            })
    
    # 获取客户列表
    customer_names = db.session.query(Customer.name).all()
    
    return render_template('statistics/stats_by_customer.html',
                         results=results,
                         customer_names=[c[0] for c in customer_names],
                         customer_name=customer_name,
                         start_date=start_date,
                         end_date=end_date)


@statistics_bp.route('/by_position')
@login_required
def stats_by_position():
    """按职务统计"""
    position = request.args.get('position', '')
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    show_online = request.args.get('show_online', 'on') == 'on'
    show_offline = request.args.get('show_offline', 'on') == 'on'
    
    results = []
    
    if position and start_date and end_date:
        # 时间范围
        start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        
        # 查询该职务的所有项目负责人
        staff_members = Staff.query.filter(Staff.position == position).all()
        
        for staff in staff_members:
            # 查询该负责人在时间范围内的商谈记录
            query = Negotiation.query.filter(
                Negotiation.staff_names.contains(staff.name),
                Negotiation.start_time >= start_datetime,
                Negotiation.start_time < end_datetime
            )
            
            # 根据选项筛选类别
            if show_online and show_offline:
                negotiations = query.all()
            elif show_online:
                negotiations = query.filter(Negotiation.category == '线上合作商谈').all()
            elif show_offline:
                negotiations = query.filter(Negotiation.category == '线下合作商谈').all()
            else:
                negotiations = []
            
            if negotiations:
                # 线上商谈统计
                online_negotiations = [n for n in negotiations if n.category == '线上合作商谈']
                online_total_duration = sum(n.duration_minutes or 0 for n in online_negotiations)
                online_count = len(online_negotiations)
                online_avg_duration = online_total_duration / online_count if online_count > 0 else 0
                
                # 线下商谈统计
                offline_negotiations = [n for n in negotiations if n.category == '线下合作商谈']
                offline_total_duration = sum(n.duration_minutes or 0 for n in offline_negotiations)
                offline_count = len(offline_negotiations)
                offline_avg_duration = offline_total_duration / offline_count if offline_count > 0 else 0
                
                # 总计统计
                total_duration = online_total_duration + offline_total_duration
                total_count = online_count + offline_count
                total_avg_duration = total_duration / total_count if total_count > 0 else 0
                
                results.append({
                    'staff_name': staff.name,
                    'position': staff.position,
                    'specific_position': staff.specific_position,
                    'project_name': staff.project_name,
                    'online_count': online_count,
                    'online_total_duration': online_total_duration,
                    'online_avg_duration': online_avg_duration,
                    'offline_count': offline_count,
                    'offline_total_duration': offline_total_duration,
                    'offline_avg_duration': offline_avg_duration,
                    'total_duration': total_duration,
                    'total_count': total_count,
                    'total_avg_duration': total_avg_duration
                })
    
    # 获取职务列表
    positions = db.session.query(Staff.position).distinct().all()
    
    return render_template('statistics/stats_by_position.html',
                         results=results,
                         positions=[p[0] for p in positions if p[0]],
                         position=position,
                         start_date=start_date,
                         end_date=end_date,
                         show_online=show_online,
                         show_offline=show_offline)


@statistics_bp.route('/weekly_ongoing')
@login_required
def weekly_ongoing():
    """每周未结束合作客户商谈时间"""
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    show_online = request.args.get('show_online', 'on') == 'on'
    show_offline = request.args.get('show_offline', 'on') == 'on'
    
    results = []
    
    if start_date and end_date:
        # 时间范围
        start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        
        # 查询未结束合作的客户
        ongoing_customers = Customer.query.filter(Customer.is_cooperation_ended == False).all()
        
        for customer in ongoing_customers:
            # 查询该客户在时间范围内的商谈记录
            query = Negotiation.query.filter(
                Negotiation.customer_name == customer.name,
                Negotiation.start_time >= start_datetime,
                Negotiation.start_time < end_datetime
            )
            
            # 根据选项筛选类别
            if show_online and show_offline:
                negotiations = query.all()
            elif show_online:
                negotiations = query.filter(Negotiation.category == '线上合作商谈').all()
            elif show_offline:
                negotiations = query.filter(Negotiation.category == '线下合作商谈').all()
            else:
                negotiations = []
            
            if negotiations:
                # 按日期分组统计
                daily_stats = {}
                
                for negotiation in negotiations:
                    date_key = negotiation.start_time.strftime('%Y-%m-%d')
                    if date_key not in daily_stats:
                        daily_stats[date_key] = {
                            'date': date_key,
                            'online_count': 0,
                            'offline_count': 0,
                            'total_count': 0,
                            'online_duration': 0,
                            'offline_duration': 0,
                            'total_duration': 0,
                            'categories': []
                        }
                    
                    stats = daily_stats[date_key]
                    duration = negotiation.duration_minutes or 0
                    
                    if negotiation.category == '线上合作商谈':
                        stats['online_count'] += 1
                        stats['online_duration'] += duration
                    else:
                        stats['offline_count'] += 1
                        stats['offline_duration'] += duration
                    
                    stats['total_count'] += 1
                    stats['total_duration'] += duration
                    stats['categories'].append(negotiation.category)
                
                # 添加到结果中
                for date_key, stats in daily_stats.items():
                    results.append({
                        'customer_name': customer.name,
                        'project_number': customer.project_number,
                        'date': stats['date'],
                        'online_count': stats['online_count'],
                        'offline_count': stats['offline_count'],
                        'total_count': stats['total_count'],
                        'online_duration': stats['online_duration'],
                        'offline_duration': stats['offline_duration'],
                        'total_duration': stats['total_duration'],
                        'main_category': '线上合作商谈' if stats['online_count'] > stats['offline_count'] else '线下合作商谈'
                    })
    
    return render_template('statistics/weekly_ongoing.html',
                         results=results,
                         start_date=start_date,
                         end_date=end_date,
                         show_online=show_online,
                         show_offline=show_offline)


@statistics_bp.route('/weekly_minmax')
@login_required
def weekly_minmax():
    """每周客户最长最短商谈记录"""
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    
    results = []
    
    if start_date and end_date:
        # 时间范围
        start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        
        # 查询时间范围内的所有客户
        customers_with_negotiations = db.session.query(Negotiation.customer_name).filter(
            Negotiation.start_time >= start_datetime,
            Negotiation.start_time < end_datetime
        ).distinct().all()
        
        for customer_tuple in customers_with_negotiations:
            customer_name = customer_tuple[0]
            
            # 获取该客户的所有商谈记录
            negotiations = Negotiation.query.filter(
                Negotiation.customer_name == customer_name,
                Negotiation.start_time >= start_datetime,
                Negotiation.start_time < end_datetime
            ).order_by(Negotiation.duration_minutes.desc()).all()
            
            if negotiations:
                # 找到最长和最短的商谈记录
                longest_negotiation = max(negotiations, key=lambda x: x.duration_minutes or 0)
                shortest_negotiation = min(negotiations, key=lambda x: x.duration_minutes or 0)
                
                # 统计线上线下次数
                online_count = len([n for n in negotiations if n.category == '线上合作商谈'])
                offline_count = len([n for n in negotiations if n.category == '线下合作商谈'])
                
                # 获取客户项目序号
                customer = Customer.query.filter_by(name=customer_name).first()
                project_number = customer.project_number if customer else ''
                
                results.append({
                    'customer_name': customer_name,
                    'project_number': project_number,
                    'online_count': online_count,
                    'offline_count': offline_count,
                    'total_negotiations': len(negotiations),
                    
                    # 最长商谈记录
                    'longest_date': longest_negotiation.start_time.strftime('%Y-%m-%d'),
                    'longest_category': longest_negotiation.category,
                    'longest_start_time': longest_negotiation.start_time.strftime('%H:%M:%S'),
                    'longest_end_time': longest_negotiation.end_time.strftime('%H:%M:%S'),
                    'longest_duration': longest_negotiation.duration_minutes or 0,
                    
                    # 最短商谈记录
                    'shortest_date': shortest_negotiation.start_time.strftime('%Y-%m-%d'),
                    'shortest_category': shortest_negotiation.category,
                    'shortest_start_time': shortest_negotiation.start_time.strftime('%H:%M:%S'),
                    'shortest_end_time': shortest_negotiation.end_time.strftime('%H:%M:%S'),
                    'shortest_duration': shortest_negotiation.duration_minutes or 0
                })
        
        # 按客户名称排序
        results.sort(key=lambda x: x['customer_name'])
    
    return render_template('statistics/weekly_minmax.html',
                         results=results,
                         start_date=start_date,
                         end_date=end_date)


@statistics_bp.route('/export_by_staff')
@login_required
def export_stats_by_staff():
    """导出按项目负责人统计数据"""
    staff_name = request.args.get('staff_name', '')
    customer_name = request.args.get('customer_name', '')
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    show_online = request.args.get('show_online', 'on') == 'on'
    show_offline = request.args.get('show_offline', 'on') == 'on'
    
    # 重新获取统计数据（复用现有逻辑）
    results = []
    
    if staff_name and start_date and end_date:
        start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        
        # 查询该负责人在时间范围内的商谈记录
        query = Negotiation.query.filter(
            Negotiation.staff_names.contains(staff_name),
            Negotiation.start_time >= start_datetime,
            Negotiation.start_time < end_datetime
        )
        
        if customer_name:
            query = query.filter(Negotiation.customer_name.contains(customer_name))
        
        # 根据选项筛选类别
        if show_online and show_offline:
            negotiations = query.all()
        elif show_online:
            negotiations = query.filter(Negotiation.category == '线上合作商谈').all()
        elif show_offline:
            negotiations = query.filter(Negotiation.category == '线下合作商谈').all()
        else:
            negotiations = []
        
        if negotiations:
            # 按客户分组统计
            customer_stats = {}
            
            for negotiation in negotiations:
                customer = negotiation.customer_name
                if customer not in customer_stats:
                    customer_stats[customer] = {
                        'customer_name': customer,
                        'online_negotiations': [],
                        'offline_negotiations': []
                    }
                
                if negotiation.category == '线上合作商谈':
                    customer_stats[customer]['online_negotiations'].append(negotiation)
                else:
                    customer_stats[customer]['offline_negotiations'].append(negotiation)
            
            # 计算统计数据
            for customer, data in customer_stats.items():
                online_negotiations = data['online_negotiations']
                offline_negotiations = data['offline_negotiations']
                
                # 线上统计
                online_total_duration = sum(n.duration_minutes or 0 for n in online_negotiations)
                online_count = len(online_negotiations)
                online_avg_duration = online_total_duration / online_count if online_count > 0 else 0
                
                # 线下统计
                offline_total_duration = sum(n.duration_minutes or 0 for n in offline_negotiations)
                offline_count = len(offline_negotiations)
                offline_avg_duration = offline_total_duration / offline_count if offline_count > 0 else 0
                
                # 总计统计
                total_duration = online_total_duration + offline_total_duration
                total_count = online_count + offline_count
                total_avg_duration = total_duration / total_count if total_count > 0 else 0
                
                results.append({
                    'customer_name': customer,
                    'online_count': online_count,
                    'online_total_duration': online_total_duration,
                    'online_avg_duration': online_avg_duration,
                    'offline_count': offline_count,
                    'offline_total_duration': offline_total_duration,
                    'offline_avg_duration': offline_avg_duration,
                    'total_duration': total_duration,
                    'total_count': total_count,
                    'total_avg_duration': total_avg_duration
                })
    
    # 创建Excel文件
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('按项目负责人统计')
    
    # 写入表头
    headers = ['客户姓名', '线上商谈次数', '线上总时长(分钟)', '线上平均时长(分钟)', 
               '线下商谈次数', '线下总时长(分钟)', '线下平均时长(分钟)',
               '总商谈次数', '总时长(分钟)', '总平均时长(分钟)']
    
    # 设置表头格式
    header_format = workbook.add_format({
        'bold': True,
        'bg_color': '#D7E4BC',
        'border': 1
    })
    
    for col, header in enumerate(headers):
        worksheet.write(0, col, header, header_format)
    
    # 写入数据
    for row, result in enumerate(results, 1):
        worksheet.write(row, 0, result['customer_name'])
        worksheet.write(row, 1, result['online_count'])
        worksheet.write(row, 2, result['online_total_duration'])
        worksheet.write(row, 3, round(result['online_avg_duration'], 2))
        worksheet.write(row, 4, result['offline_count'])
        worksheet.write(row, 5, result['offline_total_duration'])
        worksheet.write(row, 6, round(result['offline_avg_duration'], 2))
        worksheet.write(row, 7, result['total_count'])
        worksheet.write(row, 8, result['total_duration'])
        worksheet.write(row, 9, round(result['total_avg_duration'], 2))
    
    # 调整列宽
    worksheet.set_column('A:J', 15)
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'按项目负责人统计_{staff_name}_{start_date}到{end_date}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


@statistics_bp.route('/export_by_customer')
@login_required
def export_stats_by_customer():
    """导出按客户统计数据"""
    customer_name = request.args.get('customer_name', '')
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    
    results = []
    
    if customer_name and start_date and end_date:
        start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        
        negotiations = Negotiation.query.filter(
            Negotiation.customer_name == customer_name,
            Negotiation.start_time >= start_datetime,
            Negotiation.start_time < end_datetime
        ).all()
        
        if negotiations:
            online_negotiations = [n for n in negotiations if n.category == '线上合作商谈']
            offline_negotiations = [n for n in negotiations if n.category == '线下合作商谈']
            
            online_total_duration = sum(n.duration_minutes or 0 for n in online_negotiations)
            offline_total_duration = sum(n.duration_minutes or 0 for n in offline_negotiations)
            total_duration = online_total_duration + offline_total_duration
            
            time_delta = (datetime.strptime(end_date, '%Y-%m-%d') - datetime.strptime(start_date, '%Y-%m-%d')).days + 1
            
            results.append({
                'customer_name': customer_name,
                'online_count': len(online_negotiations),
                'online_total_duration': online_total_duration,
                'offline_count': len(offline_negotiations),
                'offline_total_duration': offline_total_duration,
                'total_count': len(negotiations),
                'total_duration': total_duration,
                'time_period_days': time_delta
            })
    
    # 创建Excel文件
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('按客户统计')
    
    # 写入表头
    headers = ['客户姓名', '线上商谈次数', '线上总时长(分钟)', '线下商谈次数', '线下总时长(分钟)',
               '总商谈次数', '总时长(分钟)', '统计时间段(天)']
    
    header_format = workbook.add_format({
        'bold': True,
        'bg_color': '#D7E4BC',
        'border': 1
    })
    
    for col, header in enumerate(headers):
        worksheet.write(0, col, header, header_format)
    
    # 写入数据
    for row, result in enumerate(results, 1):
        worksheet.write(row, 0, result['customer_name'])
        worksheet.write(row, 1, result['online_count'])
        worksheet.write(row, 2, result['online_total_duration'])
        worksheet.write(row, 3, result['offline_count'])
        worksheet.write(row, 4, result['offline_total_duration'])
        worksheet.write(row, 5, result['total_count'])
        worksheet.write(row, 6, result['total_duration'])
        worksheet.write(row, 7, result['time_period_days'])
    
    worksheet.set_column('A:H', 15)
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'按客户统计_{customer_name}_{start_date}到{end_date}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


@statistics_bp.route('/export_by_position')
@login_required
def export_stats_by_position():
    """导出按职务统计数据"""
    position = request.args.get('position', '')
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    show_online = request.args.get('show_online', 'on') == 'on'
    show_offline = request.args.get('show_offline', 'on') == 'on'
    
    results = []
    
    if position and start_date and end_date:
        start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        
        staff_members = Staff.query.filter(Staff.position == position).all()
        
        for staff in staff_members:
            query = Negotiation.query.filter(
                Negotiation.staff_names.contains(staff.name),
                Negotiation.start_time >= start_datetime,
                Negotiation.start_time < end_datetime
            )
            
            if show_online and show_offline:
                negotiations = query.all()
            elif show_online:
                negotiations = query.filter(Negotiation.category == '线上合作商谈').all()
            elif show_offline:
                negotiations = query.filter(Negotiation.category == '线下合作商谈').all()
            else:
                negotiations = []
            
            if negotiations:
                online_negotiations = [n for n in negotiations if n.category == '线上合作商谈']
                online_total_duration = sum(n.duration_minutes or 0 for n in online_negotiations)
                online_count = len(online_negotiations)
                online_avg_duration = online_total_duration / online_count if online_count > 0 else 0
                
                offline_negotiations = [n for n in negotiations if n.category == '线下合作商谈']
                offline_total_duration = sum(n.duration_minutes or 0 for n in offline_negotiations)
                offline_count = len(offline_negotiations)
                offline_avg_duration = offline_total_duration / offline_count if offline_count > 0 else 0
                
                total_duration = online_total_duration + offline_total_duration
                total_count = online_count + offline_count
                total_avg_duration = total_duration / total_count if total_count > 0 else 0
                
                results.append({
                    'staff_name': staff.name,
                    'position': staff.position,
                    'specific_position': staff.specific_position,
                    'project_name': staff.project_name,
                    'online_count': online_count,
                    'online_total_duration': online_total_duration,
                    'online_avg_duration': online_avg_duration,
                    'offline_count': offline_count,
                    'offline_total_duration': offline_total_duration,
                    'offline_avg_duration': offline_avg_duration,
                    'total_duration': total_duration,
                    'total_count': total_count,
                    'total_avg_duration': total_avg_duration
                })
    
    # 创建Excel文件
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('按职务统计')
    
    headers = ['员工姓名', '职务', '具体职务', '所属项目', '线上商谈次数', '线上总时长(分钟)', '线上平均时长(分钟)',
               '线下商谈次数', '线下总时长(分钟)', '线下平均时长(分钟)', '总商谈次数', '总时长(分钟)', '总平均时长(分钟)']
    
    header_format = workbook.add_format({
        'bold': True,
        'bg_color': '#D7E4BC',
        'border': 1
    })
    
    for col, header in enumerate(headers):
        worksheet.write(0, col, header, header_format)
    
    for row, result in enumerate(results, 1):
        worksheet.write(row, 0, result['staff_name'])
        worksheet.write(row, 1, result['position'])
        worksheet.write(row, 2, result['specific_position'] or '')
        worksheet.write(row, 3, result['project_name'] or '')
        worksheet.write(row, 4, result['online_count'])
        worksheet.write(row, 5, result['online_total_duration'])
        worksheet.write(row, 6, round(result['online_avg_duration'], 2))
        worksheet.write(row, 7, result['offline_count'])
        worksheet.write(row, 8, result['offline_total_duration'])
        worksheet.write(row, 9, round(result['offline_avg_duration'], 2))
        worksheet.write(row, 10, result['total_count'])
        worksheet.write(row, 11, result['total_duration'])
        worksheet.write(row, 12, round(result['total_avg_duration'], 2))
    
    worksheet.set_column('A:M', 12)
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'按职务统计_{position}_{start_date}到{end_date}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


@statistics_bp.route('/export_weekly_ongoing')
@login_required  
def export_weekly_ongoing():
    """导出每周未结束合作客户商谈时间统计"""
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    show_online = request.args.get('show_online', 'on') == 'on'
    show_offline = request.args.get('show_offline', 'on') == 'on'
    
    results = []
    
    if start_date and end_date:
        start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        
        ongoing_customers = Customer.query.filter(Customer.is_cooperation_ended == False).all()
        
        for customer in ongoing_customers:
            query = Negotiation.query.filter(
                Negotiation.customer_name == customer.name,
                Negotiation.start_time >= start_datetime,
                Negotiation.start_time < end_datetime
            )
            
            if show_online and show_offline:
                negotiations = query.all()
            elif show_online:
                negotiations = query.filter(Negotiation.category == '线上合作商谈').all()
            elif show_offline:
                negotiations = query.filter(Negotiation.category == '线下合作商谈').all()
            else:
                negotiations = []
            
            if negotiations:
                daily_stats = {}
                
                for negotiation in negotiations:
                    date_key = negotiation.start_time.strftime('%Y-%m-%d')
                    if date_key not in daily_stats:
                        daily_stats[date_key] = {
                            'date': date_key,
                            'online_count': 0,
                            'offline_count': 0,
                            'total_count': 0,
                            'online_duration': 0,
                            'offline_duration': 0,
                            'total_duration': 0
                        }
                    
                    stats = daily_stats[date_key]
                    duration = negotiation.duration_minutes or 0
                    
                    if negotiation.category == '线上合作商谈':
                        stats['online_count'] += 1
                        stats['online_duration'] += duration
                    else:
                        stats['offline_count'] += 1
                        stats['offline_duration'] += duration
                    
                    stats['total_count'] += 1
                    stats['total_duration'] += duration
                
                for date_key, stats in daily_stats.items():
                    results.append({
                        'customer_name': customer.name,
                        'project_number': customer.project_number,
                        'date': stats['date'],
                        'online_count': stats['online_count'],
                        'offline_count': stats['offline_count'],
                        'total_count': stats['total_count'],
                        'online_duration': stats['online_duration'],
                        'offline_duration': stats['offline_duration'],
                        'total_duration': stats['total_duration'],
                        'main_category': '线上合作商谈' if stats['online_count'] > stats['offline_count'] else '线下合作商谈'
                    })
    
    # 创建Excel文件
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('每周未结束合作客户商谈时间')
    
    headers = ['客户姓名', '项目序号', '日期', '线上商谈次数', '线下商谈次数', '总商谈次数',
               '线上商谈时长(分钟)', '线下商谈时长(分钟)', '总时长(分钟)', '主要商谈类别']
    
    header_format = workbook.add_format({
        'bold': True,
        'bg_color': '#D7E4BC',
        'border': 1
    })
    
    for col, header in enumerate(headers):
        worksheet.write(0, col, header, header_format)
    
    for row, result in enumerate(results, 1):
        worksheet.write(row, 0, result['customer_name'])
        worksheet.write(row, 1, result['project_number'] or '')
        worksheet.write(row, 2, result['date'])
        worksheet.write(row, 3, result['online_count'])
        worksheet.write(row, 4, result['offline_count'])
        worksheet.write(row, 5, result['total_count'])
        worksheet.write(row, 6, result['online_duration'])
        worksheet.write(row, 7, result['offline_duration'])
        worksheet.write(row, 8, result['total_duration'])
        worksheet.write(row, 9, result['main_category'])
    
    worksheet.set_column('A:J', 15)
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'每周未结束合作客户商谈时间_{start_date}到{end_date}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


@statistics_bp.route('/export_weekly_minmax')
@login_required
def export_weekly_minmax():
    """导出每周客户最长最短商谈记录统计"""
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    
    results = []
    
    if start_date and end_date:
        start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        
        customers_with_negotiations = db.session.query(Negotiation.customer_name).filter(
            Negotiation.start_time >= start_datetime,
            Negotiation.start_time < end_datetime
        ).distinct().all()
        
        for customer_tuple in customers_with_negotiations:
            customer_name = customer_tuple[0]
            
            negotiations = Negotiation.query.filter(
                Negotiation.customer_name == customer_name,
                Negotiation.start_time >= start_datetime,
                Negotiation.start_time < end_datetime
            ).order_by(Negotiation.duration_minutes.desc()).all()
            
            if negotiations:
                longest_negotiation = max(negotiations, key=lambda x: x.duration_minutes or 0)
                shortest_negotiation = min(negotiations, key=lambda x: x.duration_minutes or 0)
                
                online_count = len([n for n in negotiations if n.category == '线上合作商谈'])
                offline_count = len([n for n in negotiations if n.category == '线下合作商谈'])
                
                customer = Customer.query.filter_by(name=customer_name).first()
                project_number = customer.project_number if customer else ''
                
                results.append({
                    'customer_name': customer_name,
                    'project_number': project_number,
                    'online_count': online_count,
                    'offline_count': offline_count,
                    'total_negotiations': len(negotiations),
                    'longest_date': longest_negotiation.start_time.strftime('%Y-%m-%d'),
                    'longest_category': longest_negotiation.category,
                    'longest_start_time': longest_negotiation.start_time.strftime('%H:%M:%S'),
                    'longest_end_time': longest_negotiation.end_time.strftime('%H:%M:%S'),
                    'longest_duration': longest_negotiation.duration_minutes or 0,
                    'shortest_date': shortest_negotiation.start_time.strftime('%Y-%m-%d'),
                    'shortest_category': shortest_negotiation.category,
                    'shortest_start_time': shortest_negotiation.start_time.strftime('%H:%M:%S'),
                    'shortest_end_time': shortest_negotiation.end_time.strftime('%H:%M:%S'),
                    'shortest_duration': shortest_negotiation.duration_minutes or 0
                })
        
        results.sort(key=lambda x: x['customer_name'])
    
    # 创建Excel文件
    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet('每周客户最长最短商谈记录')
    
    headers = ['客户姓名', '项目序号', '线上商谈次数', '线下商谈次数', '总商谈次数',
               '最长商谈日期', '最长商谈类别', '最长商谈开始时间', '最长商谈结束时间', '最长商谈时长(分钟)',
               '最短商谈日期', '最短商谈类别', '最短商谈开始时间', '最短商谈结束时间', '最短商谈时长(分钟)']
    
    header_format = workbook.add_format({
        'bold': True,
        'bg_color': '#D7E4BC',
        'border': 1
    })
    
    for col, header in enumerate(headers):
        worksheet.write(0, col, header, header_format)
    
    for row, result in enumerate(results, 1):
        worksheet.write(row, 0, result['customer_name'])
        worksheet.write(row, 1, result['project_number'])
        worksheet.write(row, 2, result['online_count'])
        worksheet.write(row, 3, result['offline_count'])
        worksheet.write(row, 4, result['total_negotiations'])
        worksheet.write(row, 5, result['longest_date'])
        worksheet.write(row, 6, result['longest_category'])
        worksheet.write(row, 7, result['longest_start_time'])
        worksheet.write(row, 8, result['longest_end_time'])
        worksheet.write(row, 9, result['longest_duration'])
        worksheet.write(row, 10, result['shortest_date'])
        worksheet.write(row, 11, result['shortest_category'])
        worksheet.write(row, 12, result['shortest_start_time'])
        worksheet.write(row, 13, result['shortest_end_time'])
        worksheet.write(row, 14, result['shortest_duration'])
    
    worksheet.set_column('A:O', 12)
    
    workbook.close()
    output.seek(0)
    
    return send_file(
        output,
        as_attachment=True,
        download_name=f'每周客户最长最短商谈记录_{start_date}到{end_date}.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
